#!/bin/bash

#只需要重新计算上个月1日至今的数据
#将origin_type来源渠道字段转换为线上/线下，如果origin_type是NETSERVICE和PRESIGNUP类型，即为1线上，否则为0线下。

#! /bin/bash
SQOOP_HOME=/usr/bin/sqoop

#上个月1日
Last_Month_DATE=$(date -d "-1 month" +%Y-%m-01)

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
SET hive.enforce.bucketing=true;
SET hive.enforce.sorting=true;
SET hive.optimize.bucketmapjoin = true;
SET hive.auto.convert.sortmerge.join=true;
SET hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入数据
INSERT INTO TABLE itcast_dwm.itcast_intention_dwm PARTITION (yearinfo, monthinfo, dayinfo)
SELECT dwd.customer_id                                                                                        AS customer_id,
       dwd.create_date_time                                                                                   AS create_date_time,
       cus.area                                                                                               AS area,
       dwd.itcast_school_id                                                                                   AS itcast_school_id,
       sch.name                                                                                               AS itcast_school_name,
       dwd.deleted                                                                                            AS deleted,
       dwd.origin_type                                                                                        AS origin_type,
       dwd.itcast_subject_id                                                                                  AS itcast_subject_id,
       sub.name                                                                                               AS itcast_subject_name,
       dwd.hourinfo                                                                                           AS hourinfo,
       dwd.origin_type_stat                                                                                   AS origin_type_stat,
       IF(clue.clue_state = 'VALID_NEW_CLUES', '1',
          IF(clue.clue_state = 'VALID_PUBLIC_NEW_CLUE', '0', '-1'))                                           AS clue_state_stat,
       e.department_id                                                                                        AS tdepart_id,
       dept.name                                                                                              AS tdepart_name,
       dwd.yearinfo                                                                                           AS yearinfo,
       dwd.monthinfo                                                                                          AS monthinfo,
       dwd.dayinfo                                                                                            AS dayinfo
FROM itcast_dwd.itcast_intention_dwd dwd
LEFT JOIN itcast_ods.customer_clue clue ON clue.customer_relationship_id = dwd.rid
LEFT JOIN itcast_dimen.customer cus ON dwd.customer_id = cus.id
LEFT JOIN itcast_dimen.employee e ON dwd.creator = e.id
LEFT JOIN itcast_dimen.scrm_department dept ON e.department_id = dept.id
LEFT JOIN itcast_dimen.itcast_subject sub ON dwd.itcast_subject_id = sub.id
LEFT JOIN itcast_dimen.itcast_school sch ON dwd.itcast_school_id = sch.id
WHERE CONCAT_WS('-', dwd.yearinfo, dwd.monthinfo, dwd.dayinfo) >= '${Last_Month_DATE}'--2020-12-01;
"
